Data Cleaning

Load libraries

Code

knitr::opts_chunk$set(echo = TRUE, warning=FALSE, message=FALSE)
source('dependencies.R')

Read in Data Sets

State Item Analysis Report

Code

HSPhy_2022_StateItemDF<-read_state_item("data/2022_Physics_District_NextGenMCASItem.xlsx", 2022, "PHY" )

HSPhy_2022_StateItemDF

Code

HSPhy_2023_StateItemDF<-read_state_item("data/2023_Physics_District_NextGenMCASItem.xlsx", 2023, "PHY" )

HSPhy_2023_StateItemDF

School Item Analysis Reports

Code

HSPhy_2022_SchoolItemDF<-read_school_item("data/2022_Physics_NextGenMCASItem.xlsx", 2022, "PHY")
HSPhy_2022_SchoolItemDF

Code

HSPhy_2022_SchoolItemDF<-HSPhy_2022_SchoolItemDF%>%
  left_join(HSPhy_2022_StateItemDF, by= c('Year'='Year', 'Subject'='Subject', 'ITEM' = 'ITEM'))%>%
  mutate(`School-State Diff` = `School%`- `State%`)
HSPhy_2022_SchoolItemDF

Code

HSPhy_2023_SchoolItemDF<-read_school_item("data/2023_Physics_NextGenMCASItem.xlsx", 2023, "PHY")
HSPhy_2023_SchoolItemDF

Code

HSPhy_2023_SchoolItemDF<-HSPhy_2023_SchoolItemDF%>%
  left_join(HSPhy_2023_StateItemDF, by= c('Year'='Year', 'Subject'='Subject', 'ITEM' = 'ITEM'))%>%
  mutate(`School-State Diff` = `School%`- `State%`)

HSPhy_2023_SchoolItemDF

Code

tail(HSPhy_2023_SchoolItemDF)

Code

tail(HSPhy_2022_SchoolItemDF)

Code

HSPhy_NextGen_SchoolItemDF <- rbind(HSPhy_2022_SchoolItemDF, HSPhy_2023_SchoolItemDF)

HSPhy_NextGen_SchoolItemDF

State NextGen Achievement Reports

Code

HSPhy_2022_StateAchievementDF<-read_state_achievement("data/2022_HSSci_NextGenMCAS.xlsx", 2022, "PHY" )
HSPhy_2022_StateAchievementDF

Code

HSPhy_2023_StateAchievementDF<-read_state_achievement("data/2023_HSSci_NextGenMCAS.xlsx", 2023, "PHY" )
HSPhy_2023_StateAchievementDF

Code

HSPhy_NextGen_StateAchievementDF<-rbind(HSPhy_2022_StateAchievementDF, HSPhy_2023_StateAchievementDF)

HSPhy_NextGen_StateAchievementDF

IT301 Reports

Code

SG9_standardXWalk<-read_excel("data/NextGenMCASItemxWalk.xlsx", sheet = "HS_Phys_StandardxWalk")

IT301_test<-read_excel("data/2023_Physics_IT301 MCAS District and School Test Item Analysis Summary.xlsx", skip = 14)
IT301_test

Code

IT301_2022<-read_IT301("data/2022_Physics_IT301 MCAS District and School Test Item Analysis Summary.xlsx", 2022, "PHY" )
IT301_2022

Code

IT301_2023<-read_IT301("data/2023_Physics_IT301 MCAS District and School Test Item Analysis Summary.xlsx", 2023, "PHY" )
IT301_2023

Code

NextGenIT301<- rbind(IT301_2022, IT301_2023)

HSPhy_NextGenIT301<-NextGenIT301%>%
  left_join(SG9_standardXWalk, by = "Standard")

HSPhy_NextGenIT301

Join IT301 reports to SchoolItem performance Reports

Code

HSPhy_NextGen_SchoolIT301DF <- left_join(HSPhy_NextGen_SchoolItemDF, HSPhy_NextGenIT301, by = c("Year" = "Year", "Subject" = "Subject", "ITEM" = "ITEM"))

HSPhy_NextGen_SchoolIT301DF

Compute Key Summary Stats for IT301 Reports

Code

## Compute % Earned by Practice Category
## Compute State % Earned by Practice Category
## Compute SD by Practice Category
## Compute State % Earned by Reporting Category
## Compute SD by Reporting Category

Practice_Cat_Sum <- HSPhy_NextGen_SchoolIT301DF%>%
  group_by(`Practice Category`)%>%
  summarise(`Mean Diff` = mean(`School-State Diff`),
            `Med Diff` = median(`School-State Diff`),
            `SD Diff` = sd(`School-State Diff`))

Practice_Cat_Sum

Code

Practice_Cat_School_Sum <- HSPhy_NextGen_SchoolIT301DF%>%
  group_by(`School Name`, `Practice Category`)%>%
  summarise(`Mean Diff` = mean(`School-State Diff`),
            `Med Diff` = median(`School-State Diff`),
            `SD Diff` = sd(`School-State Diff`))

Practice_Cat_School_Sum

Join Summary Stats from IT301 Reports to State Achievement Reports

Code

#HSPhy_NextGen_PerfDF<- HSPhy_NextGen_StateAchievementDF%>%
#  select(`Year`, `Subject`, `School Code`, `Tested Students`, `Performance Level`, `Performance #Count`, `Performance%`, `State Performance%`, `Avg. Scaled Score`, `State Avg. Scaled Score`)%>%
#  left_join(HSPhy_NextGen_SchoolIT301DF, by = c("Year" = "Year", "Subject" = "Subject", "School #Code" = "School Code", "Tested Students" = "Tested Students"))

#HSPhy_NextGen_PerfDF%>%
#  select(`Year`, `School Name`, `School Code`, `Tested Students`)%>%
#  filter(`School Code` == "04120530")

# HSPhy_NextGen_PerfDF%>%
#   mutate(`Tested` = max(`Tested Students.x`, `Tested Students.y`, na.rm = TRUE))%>%
#   mutate(`Tested Diff` = `Tested Students.x` - `Tested Students.y`)%>%
#   group_by(`School Name`)%>%
#   summarise(`Diff Sum` = sum(`Tested Diff`, na.rm = TRUE),
#             `Tested` = sum(`Tested`))